<?php

class Verteiler
{
	public function getXOfListsWhereY($x, $y)
	{	
		$sql = "SELECT ".$x." FROM mails_Liste WHERE ".$y;
		
		$result = mysql_query($sql) or die("getXOfListsWhereY: ".mysql_error());
		return $result;
	}

	public function getLists()
	{	
		return $this->getXOfListsWhereY("*", "1");
	}

	public function getListByLID($lid)
	{
		return $this->getXOfListsWhereY("*", "LID='".$lid."'");
	}

	public function getListByTitle($title){	
		return $this->getXOfListsWhereY("*", "Titel='".$title."'");
	}

	public function getPIDsLinkedWithAddress($address)
	{
		$sql = "SELECT PID FROM mails_PersonHatAdresse NATURAL JOIN mails_Adressen WHERE Adresse='".$address."'";

		$result = mysql_query($sql) or die("getPIDsLinkedWithAddress: ".mysql_error());
		return $result;
	}

	public function getAdressesOfMembersByPID($pid) 
	{
		$sql = "SELECT * FROM mails_PersonHatAdresse NATURAL JOIN mails_Adressen WHERE PID='".$pid."'";

		$result = mysql_query($sql) or die("getAdressesOfMemberByPID: ".mysql_error());
		return $result;
	}

	public function getAdressByLID($lid)
	{
		$sql = "SELECT * FROM mails_ListeHatAdresse NATURAL JOIN mails_Adressen WHERE LID='".$lid."'";

		$result = mysql_query($sql) or die("getAdressByLID: ".mysql_error());
		return $result;
	}

	public function GetListMembershipByPID($pid){
		$sql = "SELECT LID FROM mails_PersonHatAdresse NATURAL JOIN mails_AdresseIstInListe WHERE PID='".$pid."'";

		$result = mysql_query($sql) or die("GetListMembershipByPID: ".mysql_error());
		return $result;
	}

	public function getUserByAdress($address){	
		$sql = "SELECT Vorname, Nachname 
					FROM 
						mails_Adressen 
							NATURAL JOIN 
						mails_PersonHatAdresse 
							NATURAL JOIN 
						mails_Personen 
					WHERE Adresse='".$address."'";
		
		$result = mysql_query($sql) or die("getUserByAdress: ".mysql_error());
		return $result;
	}
	
	public function addAddress($address){	
		$sql = "INSERT INTO mails_Adressen VALUES ('','".$address."')";
		$result = mysql_query($sql) or die("addAddress: ".mysql_error());
		return mysql_insert_id();
	}

	public function addListe($title, $descr)
	{
		$sql = "INSERT INTO mails_Liste VALUES ('','".$title."','".$descr."')";
		$result = mysql_query($sql) or die("insertNewList: ".mysql_error());
		return mysql_insert_id();		
	}


	public function addPerson($vorname, $nachname, $descr, $date){	
		$sql = "INSERT INTO mails_Personen VALUES ('','".$vorname."','".$nachname."','".$descr."','".$date."')";
		$result = mysql_query($sql) or die("insertNewMember: ".addPerson());
		return mysql_insert_id();
	}

	public function getAddress($address){	
		$sql = "SELECT * FROM mails_Adressen WHERE Adresse='".$address."'";
		
		$result = mysql_query($sql) or die("getAdress: ".mysql_error());
		return $result;
	}

	public function getAddressOfList($LID){	
		$sql = "SELECT * FROM mails_ListeHatAdresse NATURAL JOIN mails_Adressen WHERE LID='".$LID."'";
		
		$result = mysql_query($sql) or die("getAddressOfList: ".mysql_error());
		return $result;
	}

	public function getAllAdressesAndNames(){	
		$sql = "SELECT * FROM mails_Adressen NATURAL JOIN mails_PersonHatAdresse NATURAL JOIN mails_Personen ORDER BY Nachname ASC";
		
		$result = mysql_query($sql) or die("getAllAdressesAndNames: ".mysql_error());
		return $result;
	}

	public function getAllAdressesAndListtitles(){	
		$sql = "SELECT * FROM mails_Adressen NATURAL JOIN mails_ListeHatAdresse NATURAL JOIN mails_Liste ORDER BY Titel ASC";
		
		$result = mysql_query($sql) or die("getAllAdressesAndListtitles: ".mysql_error());
		return $result;
	}

	public function insertNewList($title, $descr, $address){	
		$result = $this->getListByTitle($title);
		if (mysql_num_rows($result) > 0)
		{
			return 3;
		} 
		$result = $this->getAddress($address);
		if (mysql_num_rows($result) > 0)
		{
			return 2;
		} 
		

		$LID =  $this->addListe($title, $descr);			
		$AID =  $this->addAddress($address);
		$this->addAddressOfList($LID, $AID);		
		return 1;
	}

	public function getMembersWhereY($y){	
		$sql = "SELECT * FROM mails_Personen WHERE ".$y." ORDER BY Nachname ASC";
		
		$result = mysql_query($sql) or die("getMembersWhereY: ".mysql_error());
		return $result;
	}

	public function getMembers()
	{	
		return $this->getMembersWhereY("1");
	}

	public function getMemberByName($vorname, $nachname)
	{	
		return $this->getMembersWhereY("Vorname ='".$vorname."' AND Nachname = '".$nachname."'");
	}

	public function getMemberByPID($PID)
	{	
		return $this->getMembersWhereY("PID ='".$PID."'");
	}

	public function getMembersOfListByLID($lid)
	{
		$sql = "SELECT * FROM mails_Personen NATURAL JOIN mails_PersonHatAdresse NATURAL JOIN  mails_Adressen AS mad WHERE EXISTS (SELECT * FROM mails_AdresseIstInListe AS mid WHERE mad.AID = mid.AID AND LID='".$lid."') ORDER BY Nachname ASC ";
		
		$result = mysql_query($sql) or die("getMembersOfListByLID: ".mysql_error());
		return $result;
	}
	public function getMemberListsOfListByLID($lid)
	{
		$sql = "SELECT * FROM mails_Liste NATURAL JOIN mails_ListeHatAdresse NATURAL JOIN  mails_Adressen AS mad WHERE EXISTS (SELECT * FROM mails_AdresseIstInListe AS mid WHERE mad.AID = mid.AID AND LID='".$lid."') ORDER BY Titel ASC ";
		
		$result = mysql_query($sql) or die("getMemberListsOfListByLID: ".mysql_error());
		return $result;
	}

	public function getWriteRightMembersOfListByLID($lid)
	{
		$sql = "SELECT * FROM mails_Personen NATURAL JOIN mails_PersonHatAdresse NATURAL JOIN  mails_Adressen AS mad WHERE EXISTS (SELECT * FROM mails_AdresseDarfAnschreibenListe AS mid WHERE mad.AID = mid.AID AND LID='".$lid."')";
		
		$result = mysql_query($sql) or die("getMembersOfListByLID: ".mysql_error());
		return $result;
	}

	public function getWriteRightListsOfListByLID($lid)
	{
		$sql = "SELECT * FROM mails_Liste NATURAL JOIN mails_ListeHatAdresse NATURAL JOIN  mails_Adressen AS mad WHERE EXISTS (SELECT * FROM mails_AdresseDarfAnschreibenListe AS mid WHERE mad.AID = mid.AID AND LID='".$lid."')";
		
		$result = mysql_query($sql) or die("getWriteRightListsOfListByLID: ".mysql_error());
		return $result;
	}

	public function addAddressOfList($LID,$AID)
	{
		$sql = "INSERT INTO mails_ListeHatAdresse VALUES ('".$LID."','".$AID."')";
		$result = mysql_query($sql) or die("insertNewList: ".mysql_error());
		return $result;
	}

	public function addAddressToList($AID,$LID)
	{
		$sql = "INSERT INTO mails_AdresseIstInListe VALUES ('".$AID."','".$LID."')";
		$result = mysql_query($sql) or die("addAddressToList: ".mysql_error());
		return $result;
	}


	public function addAddressToRight($AID,$LID)
	{
		$sql = "INSERT INTO mails_AdresseDarfAnschreibenListe VALUES ('".$AID."','".$LID."')";
		$result = mysql_query($sql) or die("addAddressToRight: ".mysql_error());
		return $result;
	}

	public function addAddressToPerson($PID, $AID)
	{
		$sql = "INSERT INTO mails_PersonHatAdresse VALUES ('".$PID."','".$AID."')";
		$result = mysql_query($sql) or die("addAddressToPerson: ".mysql_error());
	}

	public function UpdateList($lid, $title, $comment)
	{
		$sql = "UPDATE mails_Liste SET 
					Titel='".$title."',
					Kurzbeschreibung='".$comment."'
				WHERE LID='".$lid."'";
		$result = mysql_query($sql) or die("UpdateList: ".mysql_error());
	}	

	public function UpdateListAddress($lid, $email)
	{
		$sql = "UPDATE mails_Adressen AS ma SET Adresse='".$email."'
				WHERE EXISTS (SELECT AID FROM mails_ListeHatAdresse AS lha WHERE lha.LID='".$lid."' AND lha.AID=ma.AID)";
		$result = mysql_query($sql) or die("UpdateListAddress: ".mysql_error());
	}

	public function UpdatePerson($PID, $vorname, $nachname, $until, $comment)
	{
		$sql = "UPDATE mails_Personen SET 
					Vorname='".$vorname."' ,
					Nachname='".$nachname."' ,
					Kommentar='".$comment."' ,
					Bis='".$until."'
				WHERE PID='".$PID."'";
		$result = mysql_query($sql) or die("UpdatePerson: ".mysql_error());
	}	

	public function addNewAdress($pid,$address)
	{
		$AID = $this->addAddress($address);
		$result = $this->addAddressToPerson($pid, $AID);
		return $result;
	}

	public function insertNewMember($vorname, $nachname, $descr, $date, $address, $force, $lists)
	{	
		if (!$force)
		{
			$result = $this->getMemberByName($vorname, $nachname);
			if (mysql_num_rows($result) > 0)
			{
				return 2;
			} 
		}

		$result = $this->getAddress($address);
		if (mysql_num_rows($result) > 0)
		{
			return 3;
		} 

		$PID = $this->addPerson($vorname, $nachname, $descr, $date);
		$AID = $this->addAddress($address);
		$result = $this->addAddressToPerson($PID, $AID);

		if(!empty($lists))
  		{
    		$N = count($lists);
 
			for($i=0; $i < $N; $i++)
			{	
				$list = $lists[$i];
				$result = $this->addAddressToList($AID,$list);
			}
		}
		return 1;
	}
	
	public function getAllPIDsAssociatedWithAddress($aid)
	{
		$sql = "SELECT PID FROM mails_PersonHatAdresse WHERE AID='".$aid."'";
		
		$result = mysql_query($sql) or die("getAllPIDsAssociatedWithAddress: ".mysql_error());
		return $result;
	}

	public function removeAddressFromRightAssociation($aid)
	{
		$sql = "DELETE FROM mails_mails_AdresseDarfAnschreibenListe WHERE AID='".$aid."'";
		
		$result = mysql_query($sql) or die("removeAddressFromListAssociation: ".mysql_error());
		return $result;
	}	

	public function removeAddressFromListAssociation($aid)
	{
		$sql = "DELETE FROM mails_AdresseIstInListe WHERE AID='".$aid."'";
		
		$result = mysql_query($sql) or die("removeAddressFromListAssociation: ".mysql_error());
		return $result;
	}	

	public function removeAddressFromAddresses($aid)
	{
		$sql = "DELETE FROM mails_Adressen WHERE AID='".$aid."'";
		
		$result = mysql_query($sql) or die("removeAddressFromAddresses: ".mysql_error());
		return $result;
	}

	public function removeAdresseOfMembersByPID($aid, $pid) {
		$sql = "DELETE FROM mails_PersonHatAdresse WHERE AID='".$aid."' AND PID='".$pid."'";
		
		$result = mysql_query($sql) or die("removeAddressFromListAssociation: ".mysql_error());

		$result = $this->getAllPIDsAssociatedWithAddress($aid);		
		if (mysql_num_rows($result) == 0)
		{
			$this->removeAddressFromListAssociation($aid);
			$this->removeAddressFromAddresses($aid);
		}
		return $result;
	}
}
?>
